"""
Case Type   : 兼容M*数据库subdate函数
Case Name   : SUBDATE(TIME,intetval)函数，第一个参数为time类型,第二个参数取值测试
Create At   : 2023.01.28
Owner       : z@wan005
Description :
    1、第2个参数中表达式为负数
    2、第2个参数中表达式为负数加单引号
    3、interval前加负号
    4、表达式数字加引号
    5、表达式为空串
    6、表达式为null
    7、表达式为字符串
    8、表达式含特殊字符串
    9、表达式为正数
    10、表达式为运算
    11、参数2为整数
    12、参数2为小数
    13、参数2为负数
    14、参数2为空字符串
    15、参数2为0
    16、参数2超int8的最大取值
    17、参数2加引号
    18、参数2超int8的最大取值
    19、参数2加引号
    20、参数2为运算表达式
    21、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、弹出warning并返回结果
    6、输出warnig并返回结果
    7、弹出warning并返回结果
    8、弹出warning并返回结果
    9、成功
    10、报错
    11、成功，视为减去的天数值
    12、成功，四舍五入，减去天数
    13、成功，加上天数
    14、输出warning并打印结果
    15、成功
    16、返回warning并输出结果
    17、成功
    18、返回warning并输出结果
    19、成功
    20、成功
    21、成功
History     :modified by z@wan005，2023.09.20，step5第2个入参是空串，
             select场景下不报错，优化用例断言
             modified by zhanghuan96,2024.6.20，修改预期结果断言方式
             modified by zhanghuan，2024.8.2，适配最新代码，修改断言
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CompatibilityTest013(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.common = Common()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = "db_subdate_0013"

    def test_subdate(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.log.info('----前置条件：创建兼容b库---')
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} dbcompatibility 'b';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res)

        text = '--step1:第2个参数中表达式为负数;expect:成功--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59', interval -2 hour);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertEqual('25:59:59', sql_res.splitlines()[-2].strip(),
                         '执行失败' + text)

        text = '--step2:第2个参数中表达式为负数加单引号;expect:成功--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59', interval '-1' hour);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertEqual('24:59:59', sql_res.splitlines()[-2].strip(),
                         '执行失败' + text)

        text = '--step3:interval前加负号;expect:成功--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59', -interval 59 second);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertEqual('24:00:58', sql_res.splitlines()[-2].strip(),
                         '执行失败' + text)

        text = '--step4:表达式数字加引号;expect:成功--'
        self.log.info(text)
        sql_cmd = '''select subdate(time'23:59:59',interval '2' day);\
        set dolphin.sql_mode='sql_mode_strict';\
        select subdate(time'23:59:59',interval "2" day);\
        reset dolphin.sql_mode;'''
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('SET', sql_res, '执行失败' + text)
        self.assertEqual(2, sql_res.count('-24:00:01'), '执行失败' + text)

        text = '--step5:表达式为空串;expect:打印warning并输出结果--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59',interval '' year);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('WARNING:  invalid input syntax for numeric',
                      sql_res, '执行失败' + text)
        self.assertIn('23:59:59', sql_res, '执行失败' + text)

        text = '--step6:表达式为null;expect:输出warnig并返回结果--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59',interval '' year);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('WARNING:  invalid input syntax for numeric',
                      sql_res, '执行失败' + text)
        self.assertIn('23:59:59', sql_res, '执行失败' + text)

        text = '--step7:表达式为字符串;expect:弹出warning并返回null--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59',interval '5abc' year);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('subdate \n---------\n \n(1 row)',
                      sql_res, '执行失败' + text)

        text = '--step8:表达式含特殊字符串;expect:弹出warning并返回结果--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59',interval '5abc' year);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('WARNING:  invalid input syntax for type numeric',
                      sql_res, '执行失败' + text)
        self.assertIn('subdate \n---------\n \n(1 row)',
                      sql_res, '执行失败' + text)

        text = '--step9:表达式为正数;expect:成功--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59',interval +5 day);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('-96:00:01', sql_res, '执行失败' + text)

        text = '--step10:表达式为运算;expect:成功--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59',interval 2-1 day);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('-00:00:01', sql_res, '执行失败' + text)

        text = '--step11:参数2为整数;expect:成功，视为减去的天数值--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59', 10);" \
                  "select subdate(time'23:59:59', +10);" \
                  "select subdate(time'23:59:59', -9223372036854775808);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertTrue(len(sql_res.strip().splitlines()) >= 14, '行数不到14行')
        self.assertEqual(2, sql_res.count('-216:00:01'), '执行失败' + text)
        self.assertIn('23:59:59', sql_res, '执行失败' + text)

        text = '--step12:参数2为小数;expect:成功，四舍五入，减去天数--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59',  1.5);" \
                  "select subdate(time'23:59:59',  1.4);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertTrue(len(sql_res.strip().splitlines()) >= 9, '行数不到9行')
        self.assertEqual('-24:00:01', sql_res.splitlines()[2].strip(),
                         '执行失败' + text)
        self.assertEqual('-00:00:01', sql_res.splitlines()[-2].strip(),
                         '执行失败' + text)

        text = '--step13:参数2为负数;expect:成功，加上天数--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59',  -30);" \
                  "select subdate(time'23:59:59', -1);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertTrue(len(sql_res.strip().splitlines()) >= 9, '行数不到9行')
        self.assertEqual('743:59:59', sql_res.splitlines()[2].strip(),
                         '执行失败' + text)
        self.assertEqual('47:59:59', sql_res.splitlines()[-2].strip(),
                         '执行失败' + text)

        text = '--step14:参数2为空字符串;expect:输出warning并打印结果--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59',  '');"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('WARNING:  invalid input syntax for type bigint', sql_res,
                      '执行失败' + text)
        self.assertIn('23:59:59', sql_res, '执行失败' + text)

        text = '--step15:参数2为0;expect:成功--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59',  0);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertEqual('23:59:59', sql_res.splitlines()[-2].strip(),
                         '执行失败' + text)

        text = '--step16:参数2超int8的最大取值;expect:返回warning并输出结果--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59', 9223372036854775808);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('WARNING:  bigint out of range', sql_res,
                      '执行失败' + text)
        self.assertIn('23:59:59', sql_res, '执行失败' + text)

        text = '--step17:参数2加引号;expect:成功--'
        self.log.info(text)
        sql_cmd = '''select subdate(time'23:59:59', '-1');\
        set dolphin.sql_mode='sql_mode_strict';\
        select subdate(time'23:59:59', -1);\
        reset dolphin.sql_mode;;'''
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('SET', sql_res, '执行失败' + text)
        self.assertEqual(2, sql_res.count('47:59:59'), '执行失败' + text)

        text = '--step18:参数2超int8的最大取值;expect:返回warning并输出结果--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59', 9223372036854775808);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('23:59:59', sql_res, '执行失败' + text)

        text = '--step19:参数2加引号;expect:成功--'
        self.log.info(text)
        sql_cmd = '''select subdate(time'23:59:59', '-1');\
        set dolphin.sql_mode='sql_mode_strict';\
        select subdate(time'23:59:59', "-1");\
        reset dolphin.sql_mode;'''
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('SET', sql_res, '执行失败' + text)
        self.assertEqual(2, sql_res.count('47:59:59'), '执行失败' + text)

        text = '--step20:参数2为运算表达式;expect:成功--'
        self.log.info(text)
        sql_cmd = "select subdate(time'23:59:59',1+1);" \
                  "select subdate(time'23:59:59',2-1);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertEqual('-24:00:01', sql_res.splitlines()[2].strip(),
                         '执行失败' + text)
        self.assertEqual('-00:00:01', sql_res.splitlines()[-2].strip(),
                         '执行失败' + text)

    def tearDown(self):
        text = '----step21:清理环境;expect:成功----'
        self.log.info(text)
        sql_cmd = f"drop database {self.db_name};"
        self.log.info(sql_cmd)
        drop_res1 = self.sh_primary.execut_db_sql(sql_cmd,
                                                  dbname='postgres')
        self.log.info(drop_res1)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_res1,
                      '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
